﻿CREATE SCHEMA pca_caja;

CREATE TABLE pca_caja.tipos_movimiento
(
	tipo_movimiento_id varchar(1) NOT NULL,
	nombre varchar(20),
	CONSTRAINT tipo_movimiento_pk PRIMARY KEY(tipo_movimiento_id)
);

-- Datos de Inicio
INSERT INTO pca_caja.tipos_movimiento VALUES('I','Ingreso'),('E','Egreso');

-------------------------------------Tablas independiente por cada Empresa-Oficina-----------------------------------

CREATE SEQUENCE pca_caja.seq_grupo_movimiento_id;
CREATE TABLE pca_caja.grupos_movimiento
(
	grupo_movimiento_id bigint NOT NULL DEFAULT pca_global.fn_pseudo_encrypt_bigint(nextval('pca_caja.seq_grupo_movimiento_id')::integer),
	tipo_movimiento_id varchar(1) NOT NULL,
	empresa_id varchar(2) NOT NULL,
	oficina_id varchar(2) NOT NULL,
	sesion_id bigint NOT NULL,
	codigo varchar(20) NOT NULL,
	nombre varchar(100) NOT NULL,
	CONSTRAINT grupo_movimiento_pk PRIMARY KEY(grupo_movimiento_id,empresa_id),
	CONSTRAINT tipo_movimiento_fkey FOREIGN KEY(tipo_movimiento_id)
		REFERENCES pca_caja.tipos_movimiento(tipo_movimiento_id) MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT,
	CONSTRAINT sesiones_fkey FOREIGN KEY(empresa_id, oficina_id, sesion_id)
		REFERENCES pca_log.sesiones(empresa_id, oficina_id, sesion_id) MATCH FULL
		ON UPDATE RESTRICT ON DELETE RESTRICT,
	CONSTRAINT grupo_movimiento_id_unq UNIQUE(grupo_movimiento_id),
	CONSTRAINT grupos_movimiento_codigo_unq UNIQUE(empresa_id,codigo)
);

CREATE SEQUENCE pca_caja.seq_subgrupo_movimiento_id;
CREATE TABLE pca_caja.subgrupos_movimiento
(
	subgrupo_movimiento_id bigint NOT NULL DEFAULT pca_global.fn_pseudo_encrypt_bigint(nextval('pca_caja.seq_subgrupo_movimiento_id')::integer),
	grupo_movimiento_id bigint NOT NULL,
	empresa_id varchar(2) NOT NULL,
	oficina_id varchar(2) NOT NULL,
	sesion_id bigint NOT NULL,
	codigo varchar(20) NOT NULL,
	nombre varchar(100) NOT NULL,
	CONSTRAINT submovimiento_pk PRIMARY KEY(subgrupo_movimiento_id,empresa_id),
	CONSTRAINT grupo_movimiento_fkey FOREIGN KEY(grupo_movimiento_id,empresa_id)
		REFERENCES pca_caja.grupos_movimiento(grupo_movimiento_id,empresa_id) MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE CASCADE,
	CONSTRAINT sesiones_fkey FOREIGN KEY(empresa_id, oficina_id, sesion_id)
		REFERENCES pca_log.sesiones(empresa_id, oficina_id, sesion_id) MATCH FULL
		ON UPDATE RESTRICT ON DELETE RESTRICT,	
	CONSTRAINT subgrupo_movimiento_id_unq UNIQUE(subgrupo_movimiento_id),
	CONSTRAINT subgrupos_movimiento_codigo_unq UNIQUE(empresa_id,grupo_movimiento_id,codigo)
);

--Cada usuario puede tener varias liquidaciones por dia por ejemplo trabajando 2 turnos y liquidando 2 veces
CREATE SEQUENCE pca_caja.seq_liquidacion_id;
CREATE TABLE pca_caja.liquidaciones
(
	empresa_id varchar(2) NOT NULL,
	oficina_id varchar(2) NOT NULL,
	liquidacion_id bigint NOT NULL DEFAULT pca_global.fn_pseudo_encrypt_bigint(nextval('pca_caja.seq_liquidacion_id')::integer),
	usuario_id character(36) NOT NULL,
	fecha_hora_inicio timestamp with time zone NOT NULL DEFAULT now(),--La hora en la que se inicio la caja diaria
	cerrado boolean NOT NULL DEFAULT false,--Valor que indica si se cerro caja
	fecha_hora_cierre timestamp with time zone,
	CONSTRAINT liquidaciones_pk PRIMARY KEY(empresa_id,oficina_id,liquidacion_id,usuario_id),
	CONSTRAINT oficinas_fkey FOREIGN KEY (empresa_id, oficina_id)
		REFERENCES pca.oficinas (empresa_id, oficina_id) MATCH FULL
		ON UPDATE CASCADE ON DELETE RESTRICT,
	CONSTRAINT usuarios_fkey FOREIGN KEY (usuario_id)
		REFERENCES pca_membership."Users" ("pId") MATCH SIMPLE
		ON UPDATE CASCADE ON DELETE RESTRICT,
	CONSTRAINT liquidacion_id_unq UNIQUE(liquidacion_id)
);

CREATE OR REPLACE VIEW pca_caja.vw_liquidaciones AS
SELECT
	l.empresa_id,
	l.oficina_id,
	l.liquidacion_id,
	l.usuario_id,
	l.fecha_hora_inicio,
	l.cerrado,
	CASE WHEN l.cerrado=true THEN 'CERRADO'
	     ELSE 'ABIERTO'
	END::varchar AS estado,
	l.fecha_hora_cierre
FROM
	pca_caja.liquidaciones l;

CREATE TABLE pca_caja.resumen_liquidaciones
(
	empresa_id varchar(2) NOT NULL,
	oficina_id varchar(2) NOT NULL,
	liquidacion_id bigint NOT NULL,
	usuario_id character(36) NOT NULL,
	tipo_movimiento_id varchar(1) NOT NULL,
	moneda_id varchar(5) NOT NULL,
	monto numeric(11,3) NOT NULL,
	CONSTRAINT resumen_liquidaciones_pk PRIMARY KEY(empresa_id,oficina_id,liquidacion_id,usuario_id,tipo_movimiento_id,moneda_id),
	CONSTRAINT liquidaciones_fkey FOREIGN KEY(empresa_id,oficina_id,liquidacion_id,usuario_id)
		REFERENCES pca_caja.liquidaciones(empresa_id,oficina_id,liquidacion_id,usuario_id) MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT,
	CONSTRAINT tipo_movimiento_fkey FOREIGN KEY(tipo_movimiento_id)
		REFERENCES pca_caja.tipos_movimiento(tipo_movimiento_id) MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT,
	CONSTRAINT monedas_fkey FOREIGN KEY(moneda_id)
		REFERENCES pca_tablas.monedas(codigo) MATCH SIMPLE
		ON UPDATE CASCADE ON DELETE RESTRICT
);

CREATE OR REPLACE VIEW pca_caja.vw_resumen_liquidaciones AS
SELECT
	r.empresa_id,
	r.oficina_id,
	r.liquidacion_id,
	r.usuario_id,
	(SELECT "Nick" FROM pca_membership."UsersProfileData" WHERE "pId"=r.usuario_id)AS usuario,
	l.fecha_hora_inicio,
	l.cerrado,
	l.fecha_hora_cierre,
	r.tipo_movimiento_id,
	(SELECT nombre FROM pca_caja.tipos_movimiento WHERE tipo_movimiento_id=r.tipo_movimiento_id)AS tipo_movimiento,
	r.moneda_id,
	(SELECT siglas FROM pca_tablas.monedas WHERE codigo = r.moneda_id) AS siglas_moneda,
	r.monto
FROM
	pca_caja.resumen_liquidaciones r
INNER JOIN
	pca_caja.liquidaciones l
ON
	r.liquidacion_id = l.liquidacion_id;

--Regla para eliminar datos en la vista pca_caja.vw_resumen_liquidaciones
CREATE RULE vw_resumen_liquidaciones_rd AS ON DELETE TO pca_caja.vw_resumen_liquidaciones DO INSTEAD
	DELETE FROM pca_caja.resumen_liquidaciones WHERE liquidacion_id = OLD.liquidacion_id;

--Cada movimiento esta asignado a un unico diario
CREATE TABLE pca_caja.diarios
(
	empresa_id varchar(2) NOT NULL,
	oficina_id varchar(2) NOT NULL,
	fecha date NOT NULL,
	cerrado boolean NOT NULL DEFAULT false,--Valor que indica si se cerro el dia
	fecha_hora_cierre timestamp with time zone,
	CONSTRAINT diarios_pk PRIMARY KEY(empresa_id,oficina_id,fecha),
	CONSTRAINT oficinas_fkey FOREIGN KEY (empresa_id, oficina_id)
		REFERENCES pca.oficinas (empresa_id, oficina_id) MATCH FULL
		ON UPDATE RESTRICT ON DELETE RESTRICT
);

--Cuando el valor "cerrado" de la tabla pca_caja.diarios cambia true se calcula el resumen
CREATE TABLE pca_caja.resumen_diarios
(
	empresa_id varchar(2) NOT NULL,
	oficina_id varchar(2) NOT NULL,
	fecha date NOT NULL,
	tipo_movimiento_id varchar(1) NOT NULL,
	moneda_id varchar(5) NOT NULL,
	monto numeric(11,3) NOT NULL,
	CONSTRAINT resumen_diarios_pk PRIMARY KEY(empresa_id,oficina_id,fecha,tipo_movimiento_id,moneda_id),
	CONSTRAINT diarios_fkey FOREIGN KEY(empresa_id,oficina_id,fecha)
		REFERENCES pca_caja.diarios(empresa_id,oficina_id,fecha) MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT,
	CONSTRAINT tipo_movimiento_fkey FOREIGN KEY(tipo_movimiento_id)
		REFERENCES pca_caja.tipos_movimiento(tipo_movimiento_id) MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT,
	CONSTRAINT monedas_fkey FOREIGN KEY(moneda_id)
		REFERENCES pca_tablas.monedas(codigo) MATCH SIMPLE
		ON UPDATE CASCADE ON DELETE RESTRICT
);

CREATE OR REPLACE VIEW pca_caja.vw_resumen_diarios AS
SELECT
	r.empresa_id,
	r.oficina_id,
	r.fecha,
	d.cerrado,
	d.fecha_hora_cierre,
	r.tipo_movimiento_id,
	(SELECT nombre FROM pca_caja.tipos_movimiento WHERE tipo_movimiento_id=r.tipo_movimiento_id)AS tipo_movimiento,
	r.moneda_id,
	(SELECT siglas FROM pca_tablas.monedas WHERE codigo = r.moneda_id) AS siglas_moneda,
	r.monto
FROM
	pca_caja.resumen_diarios r
INNER JOIN
	pca_caja.diarios d
ON
	r.empresa_id = d.empresa_id AND
	r.oficina_id = d.oficina_id AND
	r.fecha = d.fecha;

CREATE SEQUENCE pca_caja.seq_movimiento_id;
CREATE TABLE pca_caja.movimientos
(
	empresa_id varchar(2) NOT NULL,
	oficina_id varchar(2) NOT NULL,
	movimiento_id bigint NOT NULL DEFAULT pca_global.fn_pseudo_encrypt_bigint(nextval('pca_caja.seq_movimiento_id')::integer),
	grupo_movimiento_id bigint NOT NULL,	
	subgrupo_movimiento_id bigint,
	sesion_id bigint NOT NULL,
	fecha date NOT NULL,
	liquidacion_id bigint,
	usuario_id character(36) NOT NULL,--El usuario que registro el movimiento
	fecha_hora_sistema timestamp with time zone NOT NULL DEFAULT now(),
	fecha_hora_actualizacion timestamp with time zone NOT NULL DEFAULT now(),
	detalle varchar(200),--Observacion es opcional
	moneda_id varchar(5) NOT NULL,
	monto numeric(11,3) NOT NULL,
	CONSTRAINT movimiento_pk PRIMARY KEY(empresa_id, oficina_id, movimiento_id),
	CONSTRAINT grupo_movimiento_fkey FOREIGN KEY(grupo_movimiento_id, empresa_id)
		REFERENCES pca_caja.grupos_movimiento(grupo_movimiento_id, empresa_id) MATCH FULL
		ON UPDATE CASCADE ON DELETE RESTRICT,
	CONSTRAINT subgrupo_movimiento_fkey FOREIGN KEY(subgrupo_movimiento_id, empresa_id)
		REFERENCES pca_caja.subgrupos_movimiento(subgrupo_movimiento_id, empresa_id) MATCH SIMPLE
		ON UPDATE CASCADE ON DELETE RESTRICT,
	CONSTRAINT sesiones_fkey FOREIGN KEY(empresa_id, oficina_id, sesion_id)
		REFERENCES pca_log.sesiones(empresa_id, oficina_id, sesion_id) MATCH FULL
		ON UPDATE RESTRICT ON DELETE RESTRICT,
	CONSTRAINT diarios_fkey FOREIGN KEY(empresa_id, oficina_id, fecha)
		REFERENCES pca_caja.diarios(empresa_id, oficina_id, fecha) MATCH FULL
		ON UPDATE RESTRICT ON DELETE RESTRICT,
	CONSTRAINT liquidaciones_fkey FOREIGN KEY(empresa_id, oficina_id, liquidacion_id, usuario_id)
		REFERENCES pca_caja.liquidaciones(empresa_id, oficina_id, liquidacion_id, usuario_id) MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT,
	CONSTRAINT usuarios_fkey FOREIGN KEY(usuario_id)
		REFERENCES pca_membership."Users"("pId") MATCH SIMPLE
		ON UPDATE CASCADE ON DELETE CASCADE,
	CONSTRAINT monedas_fkey FOREIGN KEY(moneda_id)
		REFERENCES pca_tablas.monedas(codigo) MATCH SIMPLE
		ON UPDATE CASCADE ON DELETE RESTRICT
);

CREATE OR REPLACE FUNCTION pca_caja.fn_update_fecha_hora_actualizacion_movimiento() RETURNS trigger AS
$$
BEGIN
	NEW.fecha_hora_actualizacion := now();
	RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER tri_update_fecha_hora_actualizacion_movimiento
	BEFORE UPDATE 
	ON pca_caja.movimientos
	FOR EACH ROW
	EXECUTE PROCEDURE pca_caja.fn_update_fecha_hora_actualizacion_movimiento();

CREATE OR REPLACE VIEW pca_caja.vw_movimientos AS
SELECT
	m.empresa_id,
	m.oficina_id,
	m.movimiento_id,
	g.tipo_movimiento_id,
	(SELECT nombre FROM pca_caja.tipos_movimiento WHERE tipo_movimiento_id=g.tipo_movimiento_id)AS tipo_movimiento,
	m.grupo_movimiento_id,
	g.codigo AS codigo_grupo_movimiento,
	g.nombre AS nombre_grupo_movimiento,	
	m.subgrupo_movimiento_id,
	sg.codigo AS codigo_subgrupo_movimiento,
	sg.nombre AS nombre_subgrupo_movimiento,
	m.sesion_id,
	m.fecha,
	(SELECT cerrado FROM pca_caja.diarios WHERE empresa_id = m.empresa_id AND oficina_id = m.oficina_id AND fecha = m.fecha)AS cerrado_caja_diaria,
	m.liquidacion_id,
	(SELECT cerrado FROM pca_caja.liquidaciones WHERE liquidacion_id = m.liquidacion_id)AS cerrado_liquidacion,
	m.usuario_id,
	(SELECT "Nick" FROM pca_membership."UsersProfileData" WHERE "pId"=m.usuario_id)AS usuario,
	m.fecha_hora_sistema,
	m.fecha_hora_actualizacion,
	m.detalle,
	m.moneda_id,
	(SELECT siglas FROM pca_tablas.monedas WHERE codigo = m.moneda_id) AS siglas_moneda,
	m.monto
FROM
	pca_caja.movimientos m
INNER JOIN
	pca_caja.grupos_movimiento g
ON
	m.grupo_movimiento_id = g.grupo_movimiento_id
LEFT JOIN
	pca_caja.subgrupos_movimiento sg
ON
	m.subgrupo_movimiento_id = sg.subgrupo_movimiento_id;